<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
/**
 * 
 * @copyright (c) 2013
 * @author Babak Taraghi 'babak.taraghi@gmail.com'
 * @license Apache License 2.0
 * @version 1.0
 * @desc Contains complicated queries with joins or insert transactionally 
 */
class Specific_Data_Model extends CI_Model {
	
	

	
	public function get10NearDishesByCoordinates($latitude, $longitude){
		$sql = "SELECT R.NAME AS RESTAURANT_NAME, D.idDISH AS DISH_ID, D.NAME AS DISH_NAME, D.IMAGE AS DISH_IMAGE, SQRT(
			    POW(69.1 * (LATITUDE - $latitude), 2) +
			    POW(69.1 * ($longitude - LONGITUDE) * COS(LATITUDE / 57.3), 2)) AS DISTANCE
			    FROM DISTRICT_RESTAURANT AS DR 
			    INNER JOIN RESTAURANT AS R ON DR.RESTAURANT_idRESTAURANT = R.idRESTAURANT
			    INNER JOIN RESTAURANT_DISH AS RD ON RD.RESTAURANT_idRESTAURANT = DR.RESTAURANT_idRESTAURANT 
			    INNER JOIN DISH AS D ON RD.DISH_idDISH = D.idDish 
			    HAVING DISTANCE < 10 
			    ORDER BY DISTANCE 
			    LIMIT 0,10;";
		
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	
	
	
	public function getDishes(){
		$sql = "SELECT D.idDISH AS DISH_ID, D.NAME AS DISH_NAME , RCT.NAME_TYPE AS RESTAURANT_CUISINE, DCT.NAME_TYPE AS DISH_CUISINE, D.DISH_RATE, R.NAME AS RESTAURANT_NAME, D.SERVICE_RATE, D.IMAGE AS DISH_IMAGE FROM DISH AS D
				INNER JOIN RESTAURANT_DISH AS RD ON D.idDISH = RD.DISH_idDISH
				INNER JOIN RESTAURANT AS R on RD.RESTAURANT_idRESTAURANT = R.idRESTAURANT
				INNER JOIN CUISINE_TYPE AS RCT on R.CUISINE_TYPE_idCUISINE = RCT.idCUISINE
				INNER JOIN CUISINE_TYPE AS DCT on D.CUISINE_TYPE_idCUISINE = DCT.idCUISINE;";
		
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	
	
	
	public function getDish($idDISH){
		$sql = "SELECT D.idDISH AS DISH_ID, D.NAME AS DISH_NAME , RCT.NAME_TYPE AS RESTAURANT_CUISINE, DCT.NAME_TYPE AS DISH_CUISINE, D.DISH_RATE, R.NAME AS RESTAURANT_NAME, D.SERVICE_RATE, D.IMAGE AS DISH_IMAGE FROM DISH AS D
				INNER JOIN RESTAURANT_DISH AS RD ON D.idDISH = RD.DISH_idDISH
				INNER JOIN RESTAURANT AS R on RD.RESTAURANT_idRESTAURANT = R.idRESTAURANT
				INNER JOIN CUISINE_TYPE AS RCT on R.CUISINE_TYPE_idCUISINE = RCT.idCUISINE
				INNER JOIN CUISINE_TYPE AS DCT on D.CUISINE_TYPE_idCUISINE = DCT.idCUISINE
				WHERE D.idDISH = '$idDISH'";
		
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	
	
	
	public function getCommentsForDish($idDISH){
		$sql = "SELECT P.CONTENT, U.NAME FROM POST AS P
				INNER JOIN USER AS U ON P.USER_idUSER = U.idUSER
				WHERE DISH_idDISH = '$idDISH'";
		
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	
	
	
	public function searchForRestaurants($keyword=NULL){
		$sql = "SELECT R.idRESTAURANT, R.EMAIL, R.WEBSITE, R.GRADE, R.RATE, R.NAME AS RESTAURANT_NAME, D.NAME AS DISTRICT_NAME, DR.LATITUDE, DR.LONGITUDE, DR.ADDRESS, DR.PHONE, DR.DOE_HALAL_CERT, CT.NAME_TYPE
				FROM RESTAURANT AS R 
				INNER JOIN DISTRICT_RESTAURANT AS DR ON R.idRESTAURANT = DR.RESTAURANT_idRESTAURANT
				INNER JOIN DISTRICT AS D ON D.idDISTRICT = DR.DISTRICT_idDISTRICT
				INNER JOIN CUISINE_TYPE AS CT ON CT.idCUISINE = R.CUISINE_TYPE_idCUISINE";
				if(!is_null($keyword))
					$sql .= " WHERE R.NAME LIKE '%$keyword%'";
		
		$query = $this->db->query($sql);
		return $query->result();
	}
	
	
	
	public function setDish($name, $image, $dishRate, $serviceRate, $idRESTAURANT, $idCUISINE){
		$data = array(
			'NAME'						=> $name,
			'IMAGE'						=> $image,
			'DISH_RATE'					=> $dishRate,
			'SERVICE_RATE'				=> $serviceRate,
			'CUISINE_TYPE_idCUISINE'	=> $idCUISINE
		);
		
		if(!$this->db->insert('DISH', $data)){
			$this->db->trans_complete();
			return FALSE;
		}else{
			$idDISH = $this->db->insert_id();
			
			$data = array(
				'RESTAURANT_idRESTAURANT'	=> $idRESTAURANT,
				'DISH_idDISH'				=> $idDISH
			);

			if(!$this->db->insert('RESTAURANT_DISH', $data)){
				return FALSE;
			}else{
				return $idDISH;
			}
		}
	}
	
	
	
	
	public function setPost($idDISH, $idUSER, $name, $content){
		$data = array(
			'DISH_idDISH'	=> $idDISH,
			'USER_idUSER'	=> $idUSER,
			'TITLE'			=> $name,
			'CONTENT'		=> $content,
			'CREATED_DATE'	=> date("o-m-d H:i:s"),
			'POSTED_DATE'	=> date("o-m-d H:i:s")
		);
		
		if(!$this->db->insert('POST', $data)){
			return FALSE;
		}else{
			$idPOST = $this->db->insert_id();
			
			$data = array(
				'DISH_idDISH'		=> $idDISH,
				'POST_DISH_idDISH'	=> $idDISH,
				'POST_idPOST'		=> $idPOST,
				'USER_idUSER'		=> $idUSER,
				'POST_USER_idUSER'	=> $idUSER,
				'CONTENT'			=> $content
			);

			if(!$this->db->insert('COMMENT', $data)){
				return FALSE;
			}else{
				return $idPOST;
			}
		}
	}
}